package org.abacus.dbtojava;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class DBService {
	BeanFactory beanFactory = BeanFactoryProperties.getInstance(Config.DB_TO_JAVA);
	JdbcFactory jdbcFactory = beanFactory.createBean(JdbcFactory.class);
	Jdbc jdbc = jdbcFactory.createJdbc();

	public List<String> getTables() {
		Connection conn = jdbc.createConnection();
		ResultSet rs = null;
		List<String> tables = new ArrayList<String>();
		try {
			rs = conn.getMetaData().getTables(null, null, null, Config.TABLE_NAME_TYPES);
			while (rs.next()) {
				tables.add(rs.getString(Config.TABLE_NAME_INDEX));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
		return tables;
	}

	public List<Column> getColumns(String tableNamePattern) {
		Connection conn = jdbc.createConnection();
		ResultSet rs = null;
		List<Column> list = null;
		try {
			String catalog = null;
			String schemaPattern = null;
			String columnNamePattern = null;
			rs = conn.getMetaData().getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
			list = get(rs, Column.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			if (null != conn) {
				if (null != rs) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					rs = null;
				}
				if (null != conn) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					conn = null;
				}
			}
		}
		return list;
	}

	public List<CrossReference> getCrossReferences(String parentTable, String foreignTable) {
		Connection conn = jdbc.createConnection();
		ResultSet rs = null;
		List<CrossReference> list = null;
		try {
			String parentCatalog = "";
			String parentSchema = "";
			String foreignCatalog = "";
			String foreignSchema = "";
			rs = conn.getMetaData().getCrossReference(parentCatalog, parentSchema, parentTable, foreignCatalog, foreignSchema, foreignTable);
			list = get(rs, CrossReference.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			if (null != conn) {
				if (null != rs) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					rs = null;
				}
				if (null != conn) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					conn = null;
				}
			}
		}
		return list;
	}

	public List<ExportedKey> getExportedKeys(String table) {
		Connection conn = jdbc.createConnection();
		ResultSet rs = null;
		List<ExportedKey> list = null;
		try {
			String catalog = "";
			String schema = "";
			rs = conn.getMetaData().getExportedKeys(catalog, schema, table);
			list = get(rs, ExportedKey.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			if (null != conn) {
				if (null != rs) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					rs = null;
				}
				if (null != conn) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					conn = null;
				}
			}
		}
		return list;
	}

	public List<ImportedKey> getImportedKeys(String table) {
		Connection conn = jdbc.createConnection();
		ResultSet rs = null;
		List<ImportedKey> list = null;
		try {
			String catalog = "";
			String schema = "";
			rs = conn.getMetaData().getImportedKeys(catalog, schema, table);
			list = get(rs, ImportedKey.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			if (null != conn) {
				if (null != rs) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					rs = null;
				}
				if (null != conn) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					conn = null;
				}
			}
		}
		return list;
	}

	public List<PrimaryKey> getPrimaryKeys(String tableNamePattern) {
		Connection conn = jdbc.createConnection();
		List<PrimaryKey> list = null;
		ResultSet rs = null;
		try {
			String catalog = null;
			String schema = null;
			String table = tableNamePattern;
			rs = conn.getMetaData().getPrimaryKeys(catalog, schema, table);
			list = get(rs, PrimaryKey.class);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			if (null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
		return list;
	}

	public static final <T> List<T> get(ResultSet rs, Class<T> typeOfSrc) throws SQLException, InstantiationException, IllegalAccessException {
		List<T> ts = new ArrayList<T>();
		while (rs.next()) {
			ts.add(get(rs, typeOfSrc.newInstance()));
		}
		return ts;
	}

	public static final <T> T get(ResultSet rs, T src) throws SQLException, IllegalArgumentException, IllegalAccessException {
		Class<?> c = src.getClass();
		int mod;
		while (null != c) {
			for (Field f : c.getDeclaredFields()) {
				mod = f.getModifiers();
				if (!Modifier.isFinal(mod) && !Modifier.isStatic(mod) && Modifier.isPublic(mod)) {
					f.set(src, getValue(rs, f));
				}
			}
			c = c.getSuperclass();
		}
		return src;
	}

	static Object getValue(ResultSet rs, Field f, int i) throws SQLException {
		Type type = f.getType();
		if (String.class == type) {
			return rs.getString(i);
		}
		if (int.class == type || Integer.class == type) {
			return rs.getInt(i);
		}
		if (long.class == type || Long.class == type) {
			return rs.getLong(i);
		}
		if (boolean.class == type || Boolean.class == type) {
			return rs.getBoolean(i);
		}
		if (double.class == type || Double.class == type) {
			return rs.getDouble(i);
		}
		if (short.class == type || Short.class == type) {
			return rs.getShort(i);
		}
		if (float.class == type || Float.class == type) {
			return rs.getFloat(i);
		}
		if (byte.class == type || Byte.class == type) {
			return rs.getByte(i);
		}
		if (java.util.Date.class == type || java.sql.Date.class == type) {
			return rs.getDate(i);
		}
		if (java.util.Calendar.class == type) {
			Calendar calendar = Calendar.getInstance();
			java.util.Date date = rs.getDate(i);
			calendar.setTime(date);
			return calendar;
		}
		if (BigDecimal.class == type) {
			return rs.getBigDecimal(i);
		}
		if (java.sql.Time.class == type) {
			return rs.getTime(i);
		}
		if (java.sql.Timestamp.class == type) {
			return rs.getTimestamp(i);
		}
		if (java.sql.Clob.class == type) {
			return rs.getClob(i);
		}
		if (java.sql.Blob.class == type) {
			return rs.getBlob(i);
		}
		if (java.net.URL.class == type) {
			return rs.getURL(i);
		}
		if (char.class == type || Character.class == type) {
			return rs.getString(i).charAt(0);
		}
		return rs.getObject(i);
	}

	static Object getValue(ResultSet rs, Field f) throws SQLException {
		Type type = f.getType();
		if (String.class == type) {
			return rs.getString(f.getName().toUpperCase());
		}
		if (int.class == type || Integer.class == type) {
			return rs.getInt(f.getName().toUpperCase());
		}
		if (long.class == type || Long.class == type) {
			return rs.getLong(f.getName().toUpperCase());
		}
		if (boolean.class == type || Boolean.class == type) {
			return rs.getBoolean(f.getName().toUpperCase());
		}
		if (double.class == type || Double.class == type) {
			return rs.getDouble(f.getName().toUpperCase());
		}
		if (short.class == type || Short.class == type) {
			return rs.getShort(f.getName().toUpperCase());
		}
		if (float.class == type || Float.class == type) {
			return rs.getFloat(f.getName().toUpperCase());
		}
		if (byte.class == type || Byte.class == type) {
			return rs.getByte(f.getName().toUpperCase());
		}
		if (java.util.Date.class == type || java.sql.Date.class == type) {
			return rs.getDate(f.getName().toUpperCase());
		}
		if (java.util.Calendar.class == type) {
			Calendar calendar = Calendar.getInstance();
			java.util.Date date = rs.getDate(f.getName().toUpperCase());
			calendar.setTime(date);
			return calendar;
		}
		if (BigDecimal.class == type) {
			return rs.getBigDecimal(f.getName().toUpperCase());
		}
		if (java.sql.Time.class == type) {
			return rs.getTime(f.getName().toUpperCase());
		}
		if (java.sql.Timestamp.class == type) {
			return rs.getTimestamp(f.getName().toUpperCase());
		}
		if (java.sql.Clob.class == type) {
			return rs.getClob(f.getName().toUpperCase());
		}
		if (java.sql.Blob.class == type) {
			return rs.getBlob(f.getName().toUpperCase());
		}
		if (java.net.URL.class == type) {
			return rs.getURL(f.getName().toUpperCase());
		}
		if (char.class == type || Character.class == type) {
			return rs.getString(f.getName().toUpperCase()).charAt(0);
		}
		return rs.getObject(f.getName().toUpperCase());
	}
}
